import Database from 'better-sqlite3';
import { app } from 'electron';
import path from 'path';
import fs from 'fs';
import { DATABASE_TABLES, DATABASE_INDEXES, DATABASE_VERSION, DATABASE_NAME } from './schema';

/**
 * 数据库管理器
 * 负责数据库连接、初始化和基础操作
 */
export class DatabaseManager {
  private db: Database.Database | null = null;
  private dbPath: string;

  constructor() {
    // 获取用户数据目录
    const userDataPath = app.getPath('userData');
    this.dbPath = path.join(userDataPath, DATABASE_NAME);
    
    // 确保数据目录存在
    this.ensureDataDirectory();
  }

  /**
   * 确保数据目录存在
   */
  private ensureDataDirectory(): void {
    const dataDir = path.dirname(this.dbPath);
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }
  }

  /**
   * 初始化数据库连接
   */
  public async initialize(): Promise<void> {
    try {
      console.log('正在初始化数据库:', this.dbPath);
      
      // 创建数据库连接
      this.db = new Database(this.dbPath, {
        verbose: process.env.NODE_ENV === 'development' ? console.log : undefined
      });

      // 设置数据库配置
      this.configureDatabase();

      // 创建表和索引
      await this.createTables();
      await this.createIndexes();

      // 检查和执行迁移
      await this.runMigrations();

      console.log('数据库初始化完成');
    } catch (error) {
      console.error('数据库初始化失败:', error);
      throw error;
    }
  }

  /**
   * 配置数据库参数
   */
  private configureDatabase(): void {
    if (!this.db) return;

    // 启用外键约束
    this.db.pragma('foreign_keys = ON');
    
    // 设置 WAL 模式以提高并发性能
    this.db.pragma('journal_mode = WAL');
    
    // 设置同步模式
    this.db.pragma('synchronous = NORMAL');
    
    // 设置缓存大小（10MB）
    this.db.pragma('cache_size = -10000');
    
    // 设置临时存储为内存
    this.db.pragma('temp_store = MEMORY');
    
    // 设置 mmap 大小（256MB）
    this.db.pragma('mmap_size = 268435456');
  }

  /**
   * 创建数据表
   */
  private async createTables(): Promise<void> {
    if (!this.db) throw new Error('数据库未初始化');

    console.log('正在创建数据表...');
    
    // 在事务中创建所有表
    const transaction = this.db.transaction(() => {
      Object.entries(DATABASE_TABLES).forEach(([tableName, sql]) => {
        console.log(`创建表: ${tableName}`);
        this.db!.exec(sql);
      });
    });

    transaction();
    console.log('数据表创建完成');
  }

  /**
   * 创建索引
   */
  private async createIndexes(): Promise<void> {
    if (!this.db) throw new Error('数据库未初始化');

    console.log('正在创建索引...');
    
    // 在事务中创建所有索引
    const transaction = this.db.transaction(() => {
      DATABASE_INDEXES.forEach((sql, index) => {
        console.log(`创建索引 ${index + 1}/${DATABASE_INDEXES.length}`);
        this.db!.exec(sql);
      });
    });

    transaction();
    console.log('索引创建完成');
  }

  /**
   * 运行数据库迁移
   */
  private async runMigrations(): Promise<void> {
    if (!this.db) throw new Error('数据库未初始化');

    // 检查是否存在版本表
    const versionTableExists = this.db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name='schema_version'
    `).get();

    if (!versionTableExists) {
      // 创建版本表
      this.db.exec(`
        CREATE TABLE schema_version (
          version INTEGER PRIMARY KEY,
          applied_at TEXT NOT NULL
        )
      `);
      
      // 插入当前版本
      this.db.prepare(`
        INSERT INTO schema_version (version, applied_at) 
        VALUES (?, ?)
      `).run(DATABASE_VERSION, new Date().toISOString());
      
      console.log(`数据库版本设置为: ${DATABASE_VERSION}`);
    } else {
      // 检查当前版本
      const currentVersion = this.db.prepare(`
        SELECT version FROM schema_version ORDER BY version DESC LIMIT 1
      `).get() as { version: number } | undefined;

      if (!currentVersion || currentVersion.version < DATABASE_VERSION) {
        console.log('需要执行数据库迁移...');
        // 这里可以添加具体的迁移逻辑
        // await this.executeMigrations(currentVersion?.version || 0);
      }
    }
  }

  /**
   * 获取数据库实例
   */
  public getDatabase(): Database.Database {
    if (!this.db) {
      throw new Error('数据库未初始化，请先调用 initialize()');
    }
    return this.db;
  }

  /**
   * 执行查询
   */
  public query<T = any>(sql: string, params?: any[]): T[] {
    if (!this.db) throw new Error('数据库未初始化');
    
    try {
      const stmt = this.db.prepare(sql);
      return stmt.all(params) as T[];
    } catch (error) {
      console.error('查询执行失败:', { sql, params, error });
      throw error;
    }
  }

  /**
   * 执行单条查询
   */
  public queryOne<T = any>(sql: string, params?: any[]): T | undefined {
    if (!this.db) throw new Error('数据库未初始化');
    
    try {
      const stmt = this.db.prepare(sql);
      return stmt.get(params) as T | undefined;
    } catch (error) {
      console.error('单条查询执行失败:', { sql, params, error });
      throw error;
    }
  }

  /**
   * 执行更新/插入/删除操作
   */
  public execute(sql: string, params?: any[]): Database.RunResult {
    if (!this.db) throw new Error('数据库未初始化');
    
    try {
      const stmt = this.db.prepare(sql);
      return stmt.run(params);
    } catch (error) {
      console.error('执行操作失败:', { sql, params, error });
      throw error;
    }
  }

  /**
   * 执行事务
   */
  public transaction<T>(fn: () => T): T {
    if (!this.db) throw new Error('数据库未初始化');
    
    const transaction = this.db.transaction(fn);
    return transaction();
  }

  /**
   * 开始事务
   */
  public beginTransaction(): void {
    if (!this.db) throw new Error('数据库未初始化');
    this.db.exec('BEGIN TRANSACTION');
  }

  /**
   * 提交事务
   */
  public commitTransaction(): void {
    if (!this.db) throw new Error('数据库未初始化');
    this.db.exec('COMMIT');
  }

  /**
   * 回滚事务
   */
  public rollbackTransaction(): void {
    if (!this.db) throw new Error('数据库未初始化');
    this.db.exec('ROLLBACK');
  }

  /**
   * 获取数据库统计信息
   */
  public getStats(): {
    totalPhotos: number;
    totalFolders: number;
    totalAlbums: number;
    totalTags: number;
    dbSize: number;
  } {
    if (!this.db) throw new Error('数据库未初始化');

    const totalPhotos = this.queryOne<{ count: number }>('SELECT COUNT(*) as count FROM photos WHERE deleted = 0')?.count || 0;
    const totalFolders = this.queryOne<{ count: number }>('SELECT COUNT(*) as count FROM folders')?.count || 0;
    const totalAlbums = this.queryOne<{ count: number }>('SELECT COUNT(*) as count FROM albums')?.count || 0;
    const totalTags = this.queryOne<{ count: number }>('SELECT COUNT(*) as count FROM tags')?.count || 0;
    
    // 获取数据库文件大小
    let dbSize = 0;
    try {
      const stats = fs.statSync(this.dbPath);
      dbSize = stats.size;
    } catch (error) {
      console.warn('无法获取数据库文件大小:', error);
    }

    return {
      totalPhotos,
      totalFolders,
      totalAlbums,
      totalTags,
      dbSize
    };
  }

  /**
   * 优化数据库
   */
  public optimize(): void {
    if (!this.db) throw new Error('数据库未初始化');

    console.log('正在优化数据库...');
    
    // 分析查询计划
    this.db.exec('ANALYZE');
    
    // 清理未使用的空间
    this.db.exec('VACUUM');
    
    console.log('数据库优化完成');
  }

  /**
   * 备份数据库
   */
  public async backup(backupPath: string): Promise<void> {
    if (!this.db) throw new Error('数据库未初始化');

    try {
      console.log('正在备份数据库到:', backupPath);
      
      // 确保备份目录存在
      const backupDir = path.dirname(backupPath);
      if (!fs.existsSync(backupDir)) {
        fs.mkdirSync(backupDir, { recursive: true });
      }

      // 使用 SQLite 的 backup API
      await this.db.backup(backupPath);
      
      console.log('数据库备份完成');
    } catch (error) {
      console.error('数据库备份失败:', error);
      throw error;
    }
  }

  /**
   * 关闭数据库连接
   */
  public close(): void {
    if (this.db) {
      console.log('正在关闭数据库连接...');
      this.db.close();
      this.db = null;
      console.log('数据库连接已关闭');
    }
  }

  /**
   * 检查数据库连接状态
   */
  public isConnected(): boolean {
    return this.db !== null && this.db.open;
  }
}

// 导出单例实例
export const databaseManager = new DatabaseManager();